package com.hieu.sql;

import java.io.IOException;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.drawable.Drawable;
import android.util.Log;

import com.hieu.bean.Action;
import com.hieu.bean.BarChart;
import com.hieu.bean.CircleChart;
import com.hieu.bean.Day;
import com.hieu.bean.Map;
import com.hieu.bean.StackedBarChart;
import com.hieu.bean.Trip;
import com.hieu.util.Util;

public class DAL {
	SQLiteDatabase data;

	private static String DB_PATH = "/data/data/com.hieu.tripmanager/databases/";
	private static String DB_NAME = "Database.db";
	private static final int DB_VERSION = 1;
	private DatabaseOpenHelper db;
	private final Context myContext;
	private Util util = new Util();

	public DAL(Context context) {
		myContext = context;
		db = new DatabaseOpenHelper(context);

		try {
			db.createDataBase();
		} catch (IOException ioe) {
			throw new Error("Unable to create database");
		}
	}

	private void openDb() {
		try {
			db.openDataBase();
		} catch (SQLException sqle) {
			db.close();
			throw sqle;
		}
	}

	// Lấy danh sách chuyến đi hiện tại
	public synchronized ArrayList<Trip> getCurrentTripList() {
		ArrayList<Trip> tripList = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT CHUYEN.Machuyen ,Tenchuyen, Ngaybatdau, Ngayketthuc, Loai, Tinhtrang, Ghichu, Anh "
									+ "FROM CHUYEN "
									+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "LEFT JOIN ANH ON HOATDONG.Mahoatdong = ANH.Mahoatdong "
									+ "WHERE Tinhtrang = 1 "
									+ "ORDER BY Anh DESC LIMIT 1", null);
			if (c != null && c.getCount() > 0) {
				tripList = new ArrayList<Trip>();
				Trip mTrip = null;
				while (c.moveToNext()) {
					mTrip = new Trip();
					mTrip.setIdTrip(c.getString(0));
					mTrip.setNameTrip(c.getString(1));
					mTrip.setStartDay(util.processDateString(c.getString(2)));
					mTrip.setEndDay((util.processDateString(c.getString(3))));
					mTrip.setType(c.getString(4));
					mTrip.setStatus(c.getInt(5));
					mTrip.setNotes(c.getString(6));
					mTrip.setImg(c.getString(7));

					if (mTrip != null) {
						tripList.add(mTrip);
					}

				}

			}
		}
		data.close();
		db.close();

		return tripList;
	}

	// Lấy danh sách chuyến đi trước đây
	public synchronized ArrayList<Trip> getPastTripList() {
		ArrayList<Trip> tripList = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Machuyen ,Tenchuyen, Ngaybatdau, Ngayketthuc, Loai, Tinhtrang, Ghichu "
							+ "from CHUYEN where Tinhtrang = 0 "
							+ "order by Tinhtrang DESC", null);
			if (c != null && c.getCount() > 0) {
				tripList = new ArrayList<Trip>();
				Trip mTrip = null;
				while (c.moveToNext()) {
					mTrip = new Trip();
					mTrip.setIdTrip(c.getString(0));
					mTrip.setNameTrip(c.getString(1));
					mTrip.setStartDay(util.processDateString(c.getString(2)));
					mTrip.setEndDay(util.processDateString(c.getString(3)));
					mTrip.setType(c.getString(4));
					mTrip.setStatus(c.getInt(5));
					mTrip.setNotes(c.getString(6));

					Cursor c1 = data
							.rawQuery(
									"SELECT Anh FROM CHUYEN "
											+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
											+ "LEFT JOIN ANH ON ANH.Mahoatdong = HOATDONG.Mahoatdong "
											+ "WHERE CHUYEN.Machuyen = '"
											+ c.getString(0)
											+ "' ORDER BY Anh DESC LIMIT 1",
									null);
					if (c1 != null && c1.getCount() > 0) {
						c1.moveToNext();
						mTrip.setImg(c1.getString(0));
					}

					if (mTrip != null) {
						tripList.add(mTrip);
					}

				}

			}
		}
		data.close();
		db.close();
		return tripList;
	}

	// Lấy danh sách hoạt động
	public synchronized ArrayList<Action> getActionList(String date) {
		ArrayList<Action> actionList = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"Select Mahoatdong, Ngay, Gio, Mota, Chiphi, Maloai from HOATDONG where Ngay = '"
									+ util.processDateString(date) + "'", null);
			if (c != null && c.getCount() > 0) {
				actionList = new ArrayList<Action>();
				Action mAction = null;
				while (c.moveToNext()) {
					mAction = new Action();
					mAction.setIdAction(c.getString(0));
					mAction.setDate(util.processDateString(c.getString(1)));
					mAction.setTime(c.getString(2));
					mAction.setDescription(c.getString(3));
					mAction.setAmount(c.getLong(4));
					mAction.setCate(c.getString(5));
					if (mAction != null) {
						actionList.add(mAction);
					}

				}

			}
		}
		data.close();
		db.close();
		return actionList;
	}

	// Lấy danh sách ngày trong chuyến
	public synchronized ArrayList<Day> getDayList(String idTrip) {
		ArrayList<Day> dayList = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select DISTINCT Ngay from HOATDONG where Machuyen = '"
							+ idTrip + "'", null);
			if (c != null && c.getCount() > 0) {
				dayList = new ArrayList<Day>();
				Day mDay = null;
				while (c.moveToNext()) {
					mDay = new Day();
					mDay.setDay(util.processDateString(c.getString(0)));
					if (mDay != null) {
						dayList.add(mDay);
					}

				}

			}
		}
		data.close();
		db.close();
		return dayList;
	}

	// Lấy tổng tiền của ngày
	public long getSumMoneyDate(String date) {
		long sum = 0;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select sum(Chiphi) from HOATDONG where Ngay = '"
							+ util.processDateString(date) + "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				sum = c.getLong(0);
			}
		}
		data.close();
		db.close();
		return sum;
	}

	// Lấy tổng tiền ngân sách
	public long getSumBudget() {
		long sum = 0;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery("Select sum(Chiphi) from NGANSACH", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				sum = c.getLong(0);
			}
		}
		data.close();
		db.close();
		return sum;
	}

	// Lấy tổng tiền của chuyến đi
	public long getSumMoneyTrip(String idTrip) {
		long sum = 0;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select sum(Chiphi) from HOATDONG where Machuyen = '"
							+ idTrip + "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				sum = c.getLong(0);
			}
		}
		data.close();
		db.close();
		return sum;
	}

	// Lấy danh sách các mục trong ngân sách
	public synchronized ArrayList<Long> getBudgetList() {
		ArrayList<Long> budgetList = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery("Select Maloai, Chiphi from NGANSACH",
					null);
			if (c != null && c.getCount() > 0) {
				budgetList = new ArrayList<Long>();
				while (c.moveToNext()) {
					budgetList.add(c.getLong(1));
				}

			}
		}
		data.close();
		db.close();
		return budgetList;
	}

	// Thực hiện Update ngân sách
	public void updateBudget(ArrayList<Long> list) {
		openDb();
		data = db.getWritableDatabase();

		if (data != null) {
			data.execSQL("UPDATE NGANSACH SET Chiphi = CASE Maloai "
					+ "WHEN 'C1' THEN '" + list.get(0) + "' WHEN 'C2' THEN '"
					+ list.get(1) + "' WHEN 'C3' THEN '" + list.get(2)
					+ "' WHEN 'C4' THEN '" + list.get(3) + "' WHEN 'C5' THEN '"
					+ list.get(4) + "' WHEN 'C6' THEN '" + list.get(5)
					+ "' WHEN 'C7' THEN '" + list.get(6) + "' WHEN 'C8' THEN '"
					+ list.get(7) + "' WHEN 'C9' THEN '" + list.get(8)
					+ "' WHEN 'C10' THEN '" + list.get(9) + "' END");

		}
		data.close();
		db.close();
	}

	// Lấy ngày cuối cùng trong chuyến đang thực hiện
	public String getLastDay(String idTrip) {
		String date = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"Select DISTINCT Ngay from CHUYEN LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "WHERE CHUYEN.Machuyen = '"
									+ idTrip
									+ "' order by Ngay DESC limit 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				date = c.getString(0);
				if (date != null) {
					date = util.processDateString(date);
				}
			}
		}
		data.close();
		db.close();
		return date;
	}

	// Lấy mã của chuyến đi đang thực hiện
	public String getIdNotFinishTrip() {
		String idTrip = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Machuyen from CHUYEN where Tinhtrang = 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				idTrip = c.getString(0);
			}
		}
		data.close();
		db.close();
		return idTrip;
	}

	// Lấy tên của chuyến đi đang thực hiện
	public String getNameNotFinishTrip() {
		String nameTrip = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Tenchuyen from CHUYEN where Tinhtrang = 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				nameTrip = c.getString(0);
			}
		}
		data.close();
		db.close();
		return nameTrip;
	}

	// Lấy danh sách trong thống kể biểu đồ tròn
	public ArrayList<CircleChart> getListPieChart() {
		ArrayList<CircleChart> arrayList = null;
		openDb();
		data = db.getReadableDatabase();
		if (data != null) {
			Cursor c = data
					.rawQuery(
							"Select Ten, sum(HOATDONG.Chiphi) AS Tong "
									+ "FROM CHUYEN "
									+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "LEFT JOIN NGANSACH ON HOATDONG.Maloai = NGANSACH.Maloai "
									+ "WHERE CHUYEN.Tinhtrang = 1 "
									+ "GROUP BY Ten " + "HAVING Tong > 0", null);
			if (c != null && c.getCount() > 0) {
				arrayList = new ArrayList<CircleChart>();
				CircleChart mList = null;
				while (c.moveToNext()) {
					mList = new CircleChart();
					mList.setName(c.getString(0));
					mList.setAmount(c.getLong(1));
					if (mList != null) {
						arrayList.add(mList);
					}

				}
			}
		}
		data.close();
		db.close();
		return arrayList;
	}

	// Lấy danh sách trong thống kê biểu đồ cột
	public ArrayList<BarChart> getListBarChart() {
		ArrayList<BarChart> arrayList = null;
		openDb();
		data = db.getReadableDatabase();
		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT Ngay, sum(Chiphi) "
									+ "FROM HOATDONG "
									+ "LEFT JOIN CHUYEN ON HOATDONG.Machuyen = CHUYEN.Machuyen "
									+ "WHERE Tinhtrang = 1 " + "GROUP BY Ngay",
							null);
			if (c != null && c.getCount() > 0) {
				arrayList = new ArrayList<BarChart>();
				BarChart mList = null;
				while (c.moveToNext()) {
					mList = new BarChart();
					mList.setDate(util.processDateString(c.getString(0)));
					mList.setAmount(c.getLong(1));
					if (mList != null) {
						arrayList.add(mList);
					}

				}
			}
		}
		data.close();
		db.close();
		return arrayList;
	}

	// Lấy danh sách trong thống kê biểu đồ stacked barchart
	public ArrayList<StackedBarChart> getListStackedBarChart() {
		ArrayList<StackedBarChart> arrayList = null;
		openDb();
		data = db.getReadableDatabase();
		if (data != null) {
			Cursor c = data.rawQuery(
					"SELECT Maloai, Ten, Chiphi, 0 as 'Chitieu' FROM NGANSACH",
					null);
			if (c != null && c.getCount() > 0) {
				arrayList = new ArrayList<StackedBarChart>();
				StackedBarChart mList = null;
				while (c.moveToNext()) {
					mList = new StackedBarChart();
					mList.setCate(c.getString(0));
					mList.setName(c.getString(1));
					mList.setBudget(c.getLong(2));
					mList.setFee(c.getLong(3));
					if (mList != null) {
						arrayList.add(mList);
					}

				}
			}

			c = data.rawQuery(
					"SELECT Maloai, sum(Chiphi) FROM CHUYEN "
							+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
							+ "WHERE Tinhtrang = 1 GROUP BY Maloai", null);
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					for (int i = 0; i < arrayList.size(); i++) {
						if (arrayList.get(i).getCate().equals(c.getString(0))) {
							arrayList.get(i).setFee(c.getLong(1));
						}
					}
				}
			}
		}
		data.close();
		db.close();
		return arrayList;
	}

	// Cập nhật tình trạng chuyến đi sang kết thúc
	public void updateFinishTrip(String idTrip) {
		openDb();
		data = db.getWritableDatabase();

		if (data != null) {
			data.execSQL("UPDATE CHUYEN SET Tinhtrang = 0 WHERE Machuyen = '"
					+ idTrip + "'");
			data.execSQL("UPDATE NGANSACH SET Chiphi = CASE Maloai "
					+ "WHEN 'C1' THEN 0 WHEN 'C2' THEN 0 "
					+ "WHEN 'C3' THEN 0 WHEN 'C4' THEN 0 "
					+ "WHEN 'C5' THEN 0 WHEN 'C6' THEN 0 WHEN 'C7' THEN 0 WHEN 'C8' THEN 0 WHEN 'C9' THEN 0 WHEN 'C10' THEN 0 END");
		}
		data.close();
		db.close();
	}

	// Kiểm tra mã chuyến có phải chuyến đang thực hiện
	public String checkNotFinishTrip(String idTrip) {
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Machuyen from CHUYEN where Machuyen = '" + idTrip
							+ "' and Tinhtrang = 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	// Thêm chuyến đi mới
	public void insertTrip(String name, String startDate, String endDate,
			String type, String notes) {
		openDb();
		data = db.getWritableDatabase();
		String idTrip = util.creatIdTrip(myContext);
		if (data != null) {
			data.execSQL("INSERT INTO CHUYEN(Machuyen, Tenchuyen, Ngaybatdau, Ngayketthuc, Loai, Ghichu, Tinhtrang) VALUES ('"
					+ idTrip
					+ "','"
					+ name
					+ "','"
					+ util.processDateString(startDate)
					+ "','"
					+ util.processDateString(endDate)
					+ "','"
					+ type
					+ "','"
					+ notes + "','1')");

		}
		data.close();
		db.close();
	}

	// Lấy mã hoạt động
	public String getMaxIdAction() {
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT Mahoatdong FROM CHUYEN "
									+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "ORDER BY Mahoatdong DESC LIMIT 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	// Lấy mã chuyến
	public String getMaxIdTrip() {
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT Machuyen FROM CHUYEN ORDER BY Machuyen DESC LIMIT 1",
							null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	// Thêm hoạt động
	public void insertAction(String des, String date, String time, long amount,
			String cate, ArrayList<String> listPath, String position) {
		String idAction = util.creatIdAction(myContext);
		Log.v("idAction", ""+idAction);
		String idTrip = getIdNotFinishTrip();
		if (data.isOpen())
			openDb();
		data = db.getWritableDatabase();

		if (data != null) {
			// Inserting Row
			data.execSQL("INSERT INTO HOATDONG(Mahoatdong, Machuyen, Mota, Ngay, Gio, Chiphi, Maloai, Vitri) VALUES ('"
					+ idAction
					+ "','"
					+ idTrip
					+ "','"
					+ des
					+ "','"
					+ util.processDateString(date)
					+ "','"
					+ time
					+ "', "
					+ amount + ",'" + cate + "','" + position + "')");

			if (listPath != null) {
				for (int i = 0; i < listPath.size(); i++) {
					String idImg = util.createIdImg(myContext);
					data.execSQL("INSERT INTO ANH(Maanh, Mahoatdong, Anh) VALUES ('"
							+ idImg
							+ "','"
							+ idAction
							+ "','"
							+ listPath.get(i) + "')");

				}
			}

		}
		data.close();
		db.close();
	}

	public void updateAction(String idAction, String des, String date,
			String time, String cate, Long amount, ArrayList<String> listPath) {
		// ByteArrayOutputStream stream = new ByteArrayOutputStream();
		openDb();
		data = db.getWritableDatabase();

		if (data != null) {
			// Update Row
			data.execSQL("UPDATE HOATDONG SET Mota = '" + des + "', "
					+ "Ngay = '" + util.processDateString(date) + "', "
					+ "Gio = '" + time + "', Chiphi = " + amount
					+ ", Maloai = '" + cate + "' WHERE Mahoatdong = '"
					+ idAction + "'");

			if (listPath != null) {

				data.execSQL("DELETE FROM ANH WHERE Mahoatdong = '" + idAction
						+ "'");

				for (int i = 0; i < listPath.size(); i++) {
					String idImg = util.createIdImg(myContext);
					ContentValues cv = new ContentValues();
					cv.put("Maanh", idImg);
					cv.put("Mahoatdong", idAction);
					cv.put("Anh", listPath.get(i));
					data.insert("ANH", null, cv);
				}
			}

		}
		data.close();
		db.close();
	}

	public void deleteTrip(String idTrip) {
		// TODO Auto-generated method stub
		openDb();
		ArrayList<String> listIdAction = new ArrayList<String>();
		data = db.getWritableDatabase();
		if (data != null) {
			Cursor c = data.rawQuery(
					"SELECT Mahoatdong FROM HOATDONG WHERE Machuyen = '"
							+ idTrip + "'", null);
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					listIdAction.add(c.getString(0));

				}
			}
			for (int i = 0; i < listIdAction.size(); i++) {
				data.execSQL("DELETE FROM ANH WHERE Mahoatdong = '"
						+ listIdAction.get(i) + "'");
			}
			data.execSQL("DELETE FROM HOATDONG WHERE Machuyen = '" + idTrip
					+ "'");
			data.execSQL("DELETE FROM CHUYEN WHERE Machuyen = '" + idTrip + "'");
		}
		data.close();
		db.close();
	}

	public String getMaxIdImg() {
		// TODO Auto-generated method stub
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT Maanh FROM CHUYEN "
									+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "LEFT JOIN ANH ON HOATDONG.Mahoatdong = ANH.Mahoatdong "
									+ "ORDER BY Maanh DESC LIMIT 1", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	public String checkDateTrip(String date) {
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Machuyen, Ngaybatdau from CHUYEN where Ngaybatdau = '"
							+ util.processDateString(date) + "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	public String checkDateAction(String date, String time) {
		String id = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"SELECT Mahoatdong, Ngay, Gio "
									+ "FROM CHUYEN "
									+ "LEFT JOIN HOATDONG ON CHUYEN.Machuyen = HOATDONG.Machuyen "
									+ "WHERE Ngay = '"
									+ util.processDateString(date)
									+ "' AND Gio = '" + time + "' ", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				id = c.getString(0);
			}
		}
		data.close();
		db.close();
		return id;
	}

	public ArrayList<Bitmap> getImage(String idAction) {
		ArrayList<Bitmap> listImg = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery("Select Anh from ANH where Mahoatdong = '"
					+ idAction + "'", null);
			if (c != null && c.getCount() > 0) {
				listImg = new ArrayList<Bitmap>();
				while (c.moveToNext()) {

					String path = c.getString(0);
					if (path != null) {
						Bitmap img = BitmapFactory.decodeFile(path);
						listImg.add(img);
					}

				}

			}
		}
		data.close();
		db.close();
		return listImg;
	}

	public ArrayList<String> getImagePath(String idAction) {
		ArrayList<String> listImg = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery("Select Anh from ANH where Mahoatdong = '"
					+ idAction + "'", null);
			if (c != null && c.getCount() > 0) {
				listImg = new ArrayList<String>();
				while (c.moveToNext()) {

					String path = c.getString(0);
					if (path != null) {
						listImg.add(path);
					}
				}

			}
		}
		data.close();
		db.close();
		return listImg;
	}

	public ArrayList<Bitmap> getImageTrip(String idTrip) {
		ArrayList<Bitmap> listImg = null;

		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery(
							"Select Anh from HOATDONG LEFT JOIN ANH ON HOATDONG.Mahoatdong = ANH.Mahoatdong where Machuyen = '"
									+ idTrip + "' ORDER BY Anh DESC LIMIT 5",
							null);
			if (c != null && c.getCount() > 0) {
				listImg = new ArrayList<Bitmap>();
				while (c.moveToNext()) {

					String path = c.getString(0);
					if (path != null) {
						Bitmap img = BitmapFactory.decodeFile(path);
						if (img != null) {
							listImg.add(img);
						}
					}

				}

			}
		}
		data.close();
		db.close();
		return listImg;
	}

	public void deleteDay(String day) {
		// TODO Auto-generated method stub
		openDb();
		ArrayList<String> listIdAction = new ArrayList<String>();
		data = db.getWritableDatabase();
		if (data != null) {
			Cursor c = data.rawQuery(
					"SELECT Mahoatdong FROM HOATDONG WHERE Ngay = '"
							+ util.processDateString(day) + "'", null);
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					listIdAction.add(c.getString(0));

				}
			}
			for (int i = 0; i < listIdAction.size(); i++) {
				data.execSQL("DELETE FROM ANH WHERE Mahoatdong = '"
						+ listIdAction.get(i) + "'");
			}
			data.execSQL("DELETE FROM HOATDONG WHERE Ngay = '"
					+ util.processDateString(day) + "'");
		}
		data.close();
		db.close();
	}

	public void deleteAction(String idAction) {
		// TODO Auto-generated method stub
		openDb();
		data = db.getWritableDatabase();
		if (data != null) {
			data.execSQL("DELETE FROM ANH WHERE Mahoatdong = '" + idAction
					+ "'");
			data.execSQL("DELETE FROM HOATDONG WHERE Mahoatdong = '" + idAction
					+ "'");
		}
		data.close();
		db.close();
	}

	public ArrayList<Map> getMap(String idTrip) {
		ArrayList<Map> arrayList = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"SELECT Mahoatdong, Mota, Vitri FROM HOATDONG WHERE Machuyen = '"
							+ idTrip + "'", null);
			if (c != null && c.getCount() > 0) {
				arrayList = new ArrayList<Map>();
				Map mList = null;
				while (c.moveToNext()) {
					mList = new Map();
					mList.setIdAction(c.getString(0));
					mList.setDes(c.getString(1));
					mList.setPosition(c.getString(2));

					if (mList != null) {
						arrayList.add(mList);
					}

				}
			}
		}
		data.close();
		db.close();
		return arrayList;
	}

	public long getSumFeeCate(String cate) {
		long sum = 0;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select sum(Chiphi) from HOATDONG where Maloai = '" + cate
							+ "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				sum = c.getLong(0);
			}
		}
		data.close();
		db.close();
		return sum;

	}

	public long getSumBudgetCate(String cate) {
		long sum = 0;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data
					.rawQuery("Select Chiphi from NGANSACH where Maloai = '"
							+ cate + "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				sum = c.getLong(0);
			}
		}
		data.close();
		db.close();
		return sum;

	}

	public String getFinishDate(String id) {
		String date = null;
		openDb();
		data = db.getReadableDatabase();

		if (data != null) {
			Cursor c = data.rawQuery(
					"Select Ngayketthuc from CHUYEN where Machuyen = '" + id
							+ "'", null);
			if (c != null && c.getCount() > 0) {
				c.moveToNext();
				date = util.processDateString(c.getString(0));
			}
		}
		data.close();
		db.close();
		return date;

	}
}
